<?php

namespace App\Http\Controllers\Admin;
use App\Model\Area;
use App\Model\Checkcategory;
use App\Model\Users;
use App\Model\StaffCheck;
use App\Model\StaffLeave;
use App\Model\Company;
use App\Model\Commanager;
use App\Model\Department;
use Illuminate\Http\Request;
use App\Model\Trajectory;
use App\Http\Requests;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
require '../vendor/phpoffice/phpexcel/Classes/PHPExcel.php';
require '../vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php';
class StaffController extends Controller
{
	
    /**
     * @param Request $quest
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
	public function index()
	{	
		$leavenum = StaffLeave::where('status',0)->count();
		
		return view('admin/staff/index',compact('leavenum'));
	}
	public function index2()
	{	
		return view('admin/staff/index2');
	}
	public function data(Request $request)
	{
		$query=Users::whereDoesntHave('roles', function ($query) {
			$query->where('role_id', 13);
		})->where('type',1)->where('is_delete',0)->orderBy('users.id','asc');
		if(isset($request['keyword'])){
			$query->where('name','like','%'.$request['keyword'].'%');
		}
		$res=$query->paginate($request->get('limit',15))->toArray();

		foreach($res['data'] as &$a)
		{
			$check = StaffCheck::where('user_id',$a['id'])->where('day',date("Y-m-d"))->select('created','address','image')->get()->toarray();
			$a['ss'] = isset($check[0]['created'])?date("Y-m-d H:i:s",$check[0]['created']):' ';
			$a['sx'] = isset($check[1]['created'])?date("Y-m-d H:i:s",$check[1]['created']):' ';
			$a['xs'] = isset($check[2]['created'])?date("Y-m-d H:i:s",$check[2]['created']):' ';
			$a['xx'] = isset($check[3]['created'])?date("Y-m-d H:i:s",$check[3]['created']):' ';
			$b = array_pop($check);
			$a['address'] = isset($b['address'])?$b['address']:' ';
			$a['image'] = isset($b['image'])?$b['image']:' ';
		}
		$data = [
			'code' => 0,
			'msg'   => '请求成功',
			'count' => $res['total'],
			'data'  => $res['data']
		];
		return response()->json($data);
	}
	
	public function trajectory(Request $request)
	{
		$start = strtotime($request['day']);
		$end = $start + 86399;
		$trajectory = Trajectory::where('staffid',$request['uid'])->whereBetween('time',[$start,$end])->select('trajectory')->get()->toarray();
		$i = 1;
		foreach($trajectory as &$t)
		{
			$t['id'] = $i;
			$i++;
		}
		return view('admin/staff/trajectory')->with('trajectory',$trajectory);
	}
	
	public function details(Request $request)
	{
		$strat = date("Y-m-01", time());
		$end  = strtotime("$strat +1 month -1 day");
		$strat = strtotime($strat);
		$details = StaffCheck::where('user_id',$request['id'])->whereBetween('created',[$strat,$end])->groupBy('day')->orderBy('id','desc')->select('day')->get()->toarray();
		foreach($details as $k=>$v)
		{
			$details[$k] = StaffCheck::where('user_id',$request['id'])->where('day',$v['day'])->get()->toarray();
		}

		foreach ($details as &$v){
			foreach($v as &$k)
			{
				$k['created'] = date('y-m-d H:i:s',$k['created']).' '.$k['address'];
				$k['created'] = $k['status']==3?' ':$k['created'];
			}
		}
		return view('admin/staff/details')->with('details',$details);
	}
	
	public function statistics(Request $request)
	{
		if($request->get('month')){
		    $days = date("t", strtotime($request->get('month')));
		    $month_first = date("Y-m-01", strtotime($request->get('month'))); //当月的第一天
		}else{
		    $days = date("t");
		    $month_first = date("Y-m-01", time()); //当月的第一天
		}
		for ($i = 0; $i < intval($days); $i++) {
		    # 获取当月每天
		    $day[] = date('Y-m-d', strtotime("+" . $i . " day", strtotime(date("Y-m-01",strtotime($month_first)))));
		}
		foreach ($day as $key => $value) {
		    if (date('N', strtotime($value)) > 0 && date('N', strtotime($value)) < 5) {
		        $gongzuo[] =  $value;
		    } 
		    if(date('N', strtotime($value)) == 7)
		    {
		        $gongzuo[] =  $value;
		    }
		}       
		$days = count($gongzuo);
		$month_last  = date("Y-m-d", strtotime("$month_first +1 month -1 day"));//当月的最后一天，strtotime: 以$month_first的时间为基准，(+1 month)月份加1，(-1 day)天数-1(刚好是上个月的最后一天,strtotime返回的是时间戳，用date函数转格式)
		$strat = strtotime($month_first);
		$end = strtotime($month_last) + 86400;
		
		$user = Users::where('role','normal')->where('is_delete',0)->with(['department'])->get()->toarray();
		$i = 1;
		foreach($user as &$u)
		{
			$u['late'] = StaffCheck::where('user_id',$u['id'])->where('status',1)->whereBetween('created',[$strat,$end])->get()->count();
			$u['early'] = StaffCheck::where('user_id',$u['id'])->where('status',2)->whereBetween('created',[$strat,$end])->get()->count();
			$u['days'] = StaffCheck::where('user_id',$u['id'])->whereNotIN('status',[3,4])->whereBetween('created',[$strat,$end])->groupBy('day')->get()->count();
			$leave = StaffLeave::where('userid',$u['id'])->where('status',1)->whereBetween('starttime',[$month_first,$month_last])->get()->toarray();
			$a = 0;
			foreach($leave as $l)
			{
				$a += floor((strtotime($l['endtime']) - strtotime($l['starttime']))/86400)+1;
			}
			$u['leave'] = $a;
			$u['i'] = $i++;
		}

		$department = Department::select('id','department')->get()->toarray();
		
		return view('admin/staff/statistics',compact('days','user','department'));
	}
	
	public function leave()
	{	
		$leave = StaffLeave::with(['name'])->orderBy('status','asc')->orderBy('id','desc')->get()->toarray();
		foreach($leave as &$l)
		{
			if($l['status'] == 0)
			{
				$l['reasons'] = '未审核';
			}
			if($l['status'] == 1)
			{
				$l['reasons'] = '审核通过';
			}
			if($l['status'] == 2)
			{
				$l['reasons'] = '驳回';
			}
		}
		return view('admin/staff/leave',compact('leave'));
	}
	
	public function examine(Request $request)
	{
		$StaffLeave = StaffLeave::find($request->get('id'));
		$StaffLeave->ruserid = Auth::guard('admin')->user()->id;
		if($request->get('status') == 1){
			$StaffLeave->status = 1;
			$StaffLeave->save();
			return redirect("admin/staff/leave");
		}elseif($request->get('status') == 2){
			$StaffLeave->status = 2;
			$StaffLeave->reject = $request->get('reject');
			$StaffLeave->save();
			return redirect("admin/staff/leave");
		}
	}
	
	
	public function export(Request $request)
	{
	    if($request->get('month')){
	        $days = date("t", strtotime($request->get('month')));
	        $month_first = date("Y-m-01", strtotime($request->get('month'))); //当月的第一天
	    }else{
	        $days = date("t");
	        $month_first = date("Y-m-01", time()); //当月的第一天
	    }
	    for ($i = 0; $i < intval($days); $i++) {
	        # 获取当月每天
	        $day[] = date('Y-m-d', strtotime("+" . $i . " day", strtotime(date("Y-m-01",strtotime($month_first)))));
	    }
	    foreach ($day as $key => $value) {
	        if (date('N', strtotime($value)) > 0 && date('N', strtotime($value)) < 5) {
	            $gongzuo[] =  $value;
	        } 
	        if(date('N', strtotime($value)) == 7)
	        {
	            $gongzuo[] =  $value;
	        }
	    }       
	    $days = count($gongzuo);
	    
	    $month_last  = date("Y-m-d", strtotime("$month_first +1 month -1 day"));//当月的最后一天，strtotime: 以$month_first的时间为基准，(+1 month)月份加1，(-1 day)天数-1(刚好是上个月的最后一天,strtotime返回的是时间戳，用date函数转格式)
	    $strat = strtotime($month_first);
	    $end = strtotime($month_last) + 86400;
		
		$users = Users::where('role','normal')->where('is_delete',0);
		if($request->get('department'))
		{
			$users->where('department_id',$request->get('department'));
		}
		$user = $users->select('id','name')->get()->toarray();
		foreach($user as &$u)
		{
			$u['late'] = StaffCheck::where('user_id',$u['id'])->where('status',1)->whereBetween('created',[$strat,$end])->select('day')->count();
			$u['early'] = StaffCheck::where('user_id',$u['id'])->where('status',2)->whereBetween('created',[$strat,$end])->select('day')->count();
		}
		return $user;
	}
	
	public function import(Request $request){  
		if(!$request->hasFile('file')){  
			exit('上传文件为空！');  
		}  
		$file = $_FILES;  
		$excel_file_path = $file['file']['tmp_name'];  
	    $res = [];    
	    Excel::load($excel_file_path, function($reader) use( &$res ) {    
			$reader = $reader->getSheet(0);    
			$res = $reader->toArray();    
		});

		for($i = 1;$i<count($res);$i++){  
			$com = Company::where('comName', $res[$i][0])->first();
			if($com)
			{
				$com->address = $res[$i][1];
				$com->accompany = $res[$i][7];
				$com->workersNum = $res[$i][12];
				$com->license = $res[$i][13];
				$com->photo = $res[$i][14];
				$com->save();
				$id = $com->id;
			}else{
				$c = new Company();
				$c->comName = $res[$i][0];
				$c->address = $res[$i][1];//地址
				$c->accompany = $res[$i][7];//陪同人员
				$c->workersNum = $res[$i][12];//人数
				$c->leader = $res[$i][2];//法人
				$c->leaderPhone = $res[$i][3];//法人电话
				$c->license = $res[$i][13];//营业执照
				$c->photo = $res[$i][14];//门头
				$c->save();
				$id = $c->id;
			}

			$Commanager = new Commanager();
			$Commanager->companyId = $id;
			$Commanager->name = $res[$i][9];//主要负责人
			$Commanager->phone = $res[$i][10];//负责人电话
			$Commanager->wx = $res[$i][11];//负责人微信
			$Commanager->save();
		}
	}
	public function daochu(Request $request)
	{	
		$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
		$objPHPExcel = $objReader->load("storage/excel/北宅统计.xlsx");
		$objPHPExcel->createSheet();
		$objPHPExcel->setActiveSheetIndex(0);
		$objPHPExcel->getActiveSheet()->setTitle("考勤统计");
		$users = Users::where('role','normal')->where('is_delete',0);
		if($request->get('department'))
		{
			$users->where('department_id',$request->get('department'));
		}
		$user = $users->select('id','name')->get()->toarray();
		$strat = date("Y-m-01", time());
		if($request->get('month')){
			$strat = date("Y-m-01", strtotime($request->get('month')));
		}
		$end  = strtotime("$strat +1 month -1 day") + 86399;
		$strat = strtotime($strat);
		foreach($user as &$u)
		{
			$u['check']  = StaffCheck::where('user_id',$u['id'])->whereBetween('created',[$strat,$end])->get()->toarray();	
		}
		foreach($user as &$check)
		{
			$checks = [];
			for($i = 0;$i<124;$i++) {
				$checks[$i] = '';
			}
			if(!empty($check['check']))
			{
				foreach($check['check'] as $cc)
				{
					$d = date("d",strtotime($cc['day']));
					for($i = 1; $i<=31;$i++)
					{
						if($i == $d)
						{
							$index = ($i-1)*4+$cc['zhuangtai'];
							$checks[$index] = date('Y-m-d H:i:s',$cc['created']).$cc['address'];
						}
					}
				}
				$check['c'] = $checks;
			}else{
				$check['c'] = $checks;
			}
		}
		
		$column = self::getCol(125);
		array_shift($column);
		$row = 4;
		foreach($user as $us)
		{	
			$objPHPExcel->getActiveSheet()->setCellValue("A".$row,$us['name']);
			foreach($column as $c => $index)
			{
				
				$objPHPExcel->getActiveSheet()->setCellValue($index.$row,$us['c'][$c]);
			}
			$row++;
		}

		$objPHPExcel->createSheet();
		$objPHPExcel->setActiveSheetIndex(1);
		$objPHPExcel->getActiveSheet()->setTitle("统计");
		
		$objActSheet = $objPHPExcel->getActiveSheet();
		$letter = array('A','B','C','D');
		$objActSheet->setCellValue("A1","考勤统计表");
		$objActSheet->mergeCells('A1:D1');
		$objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
		$objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
		$objActSheet->getStyle('A1')->getFont()->setName('黑体');
		$objActSheet->getStyle('A1')->getFont()->setSize(20);
		$objActSheet->getStyle('A1')->getFont()->setBold(true);
		//part1
		$objActSheet->setCellValue("A2","序号");
		$objActSheet->setCellValue("B2","姓名");
		$objActSheet->setCellValue("C2","迟到次数");
		$objActSheet->setCellValue("D2","早退次数");
		//统一设置左右居中、字号、宽度
		foreach ($letter as $l){
		    $objActSheet->getStyle($l.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
		    $objActSheet->getStyle($l.'2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
		    $objActSheet->getStyle($l.'1')->getFont()->setSize(13);
		    $objActSheet->getStyle($l.'2')->getFont()->setSize(13);
		    $objActSheet->getColumnDimension($l)->setWidth(19);
		}
		
		$tongji = self::export($request);
		$a = 1;
		$orirow = 3;
		foreach ($tongji as $aa) {
		    $objActSheet->setCellValue("A".$orirow,$a);
			$objActSheet->setCellValue("B".$orirow,$aa['name']);
		    $objActSheet->setCellValue("C".$orirow,$aa['late']);
		    $objActSheet->setCellValue("D".$orirow,$aa['early']);
		    
		    $a ++;
		    $orirow ++;
		}
		foreach ($letter as $l){
		    for ($x = 3;$x<=$orirow;$x++){
		        $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
		        $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
		    }
			
		}
		for ($x=1; $x<=($orirow); $x++) {
		    $objActSheet->getRowDimension($x)->setRowHeight(30);//统一设置行高度
		}
		$styleArray = array(
		    'borders' => array(
		        'allborders' => array(
		            'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
		        ),
		    ),
		);
		$objActSheet->getStyle('A1:D'.($orirow))->applyFromArray($styleArray);//设置粗边框
		
		$objPHPExcel->createSheet();
		$objPHPExcel->setActiveSheetIndex(2);
		$objPHPExcel->getActiveSheet()->setTitle("请假统计");
		$objActSheet = $objPHPExcel->getActiveSheet();
		$letter = array('A','B','C','D','E','F');
		$objActSheet->setCellValue("A1","请假统计表");
		$objActSheet->mergeCells('A1:F1');
		$objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
		$objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
		$objActSheet->getStyle('A1')->getFont()->setName('黑体');
		$objActSheet->getStyle('A1')->getFont()->setSize(20);
		$objActSheet->getStyle('A1')->getFont()->setBold(true);
		//part1
		$objActSheet->setCellValue("A2","姓名");
		$objActSheet->setCellValue("B2","请假开始日期");
		$objActSheet->setCellValue("C2","请假结束日期");
		$objActSheet->setCellValue("D2","理由");
		$objActSheet->setCellValue("E2","是否通过");
		$objActSheet->setCellValue("F2","驳回理由");
		//统一设置左右居中、字号、宽度
		foreach ($letter as $l){
		    $objActSheet->getStyle($l.'1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
		    $objActSheet->getStyle($l.'2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
		    $objActSheet->getStyle($l.'1')->getFont()->setSize(13);
		    $objActSheet->getStyle($l.'2')->getFont()->setSize(13);
		    $objActSheet->getColumnDimension($l)->setWidth(19);
		}
		$leave = StaffLeave::with(['name'])->get()->toarray();
		foreach($leave as &$lea)
		{
			if($lea['status'] == 0){
				$lea['status'] = '未审核';
			}
			if($lea['status'] == 1){
				$lea['status'] = '审核通过';
			}
			if($lea['status'] == 2){
				$lea['status'] = '驳回';
			}
		}
		$orirow = 3;
		foreach ($leave as $l) {
		    $objActSheet->setCellValue("A".$orirow,$l['name']['name']);
			$objActSheet->setCellValue("B".$orirow,$l['starttime']);
		    $objActSheet->setCellValue("C".$orirow,$l['endtime']);
		    $objActSheet->setCellValue("D".$orirow,$l['reason']);
		    $objActSheet->setCellValue("E".$orirow,$l['status']);
			$objActSheet->setCellValue("F".$orirow,$l['reject']);
		    $orirow ++;
		}
		foreach ($letter as $l){
		    for ($x = 3;$x<=$orirow;$x++){
		        $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
		        $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
		    }
			
		}
		for ($x=1; $x<=($orirow); $x++) {
		    $objActSheet->getRowDimension($x)->setRowHeight(30);//统一设置行高度
		}
		$styleArray = array(
		    'borders' => array(
		        'allborders' => array(
		            'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
		        ),
		    ),
		);
		$objActSheet->getStyle('A1:F'.($orirow))->applyFromArray($styleArray);//设置粗边框
		
		
		$write = new \PHPExcel_Writer_Excel5($objPHPExcel);
		ob_end_clean();
		header("Pragma: public");
		header("Expires: 0");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Content-Type:application/force-download");
		header("Content-Type:application/vnd.ms-execl");
		header("Content-Type:application/octet-stream");
		header("Content-Type:application/download");;
		header('Content-Disposition:attachment;filename="'."考勤统计".'.xls"');
		header("Content-Transfer-Encoding:binary");
		$write->save('php://output');

		exit();
	}
	
	/**
	 * @param $count 列的数量
	 * @return array 列名一维数组
	 */
	public static function getCol($count)
	{
	    $columnFlag = [
	        0 => 'Z', 1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E', 6 => 'F', 7 => 'G', 8 => 'H',
	        9 => 'I', 10 => 'J', 11 => 'K', 12 => 'L', 13 => 'M', 14 => 'N', 15 => 'O', 16 => 'P', 17 => 'Q',
	        18 => 'R', 19 => 'S', 20 => 'T', 21 => 'U', 22 => 'V', 23 => 'W', 24 => 'X', 25 => 'Y', 26 => 'Z'
	    ];
	
	    if ($count == 0) {
	        return [];
	    }
	
	    $column = [];
	    for ($index = 1; $index <= $count; $index++) {
	        if ($index <= 26) {
	            $column[] = $columnFlag[$index];
	        } else {
	            $value = floor($index / 26);
	            if ($index % 26 == 0) {
	                $value -= 1;
	            }
	            $column[] = $columnFlag[$value] . $columnFlag[floor($index % 26)];
	        }
	    }
	    return $column;
	}

}